﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class NegativeFile
    {
        public NegativeFile()
        { 
        }

        public List<NegativeFileInfo> GetList(string name)
        {
            List<NegativeFileInfo> list = new List<NegativeFileInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_NEGATIVE_FILE WHERE (NAM_CUST_FULL LIKE '%' + @NAME + '%') ORDER BY NAM_CUST_FULL");
            db.AddInParameter(cmd, "@NAME", DbType.String, name);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new NegativeFileInfo(dr.GetGuid(0), dr.GetInt32(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetString(5), dr.GetInt32(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetDouble(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetDateTime(17), dr.GetString(18), dr.GetDateTime(19)));
                }
                dr.Close();
            }
            return list;
        }

        public NegativeFileInfo GetApprovalByID(string RowID)
        {
            NegativeFileInfo ci = new NegativeFileInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_NEGATIVE_FILE_TMP WHERE ROW_ID = @ROW_ID");
            db.AddInParameter(cmd, "@ROW_ID", DbType.String, RowID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new NegativeFileInfo(dr.GetGuid(0), dr.GetInt32(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetString(5), dr.GetInt32(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetDouble(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetDateTime(17), dr.GetString(18), dr.GetDateTime(19));
                }
                dr.Close();
            }
            return ci;
        }

        public List<NegativeFileInfo> GetListApproval(string name, string UpdateBy)
        {
            List<NegativeFileInfo> list = new List<NegativeFileInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_NEGATIVE_FILE_TMP WHERE (NAM_CUST_FULL LIKE '%' + @NAME + '%') AND (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '') ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@NAME", DbType.String, name);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new NegativeFileInfo(dr.GetGuid(0), dr.GetInt32(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetString(5), dr.GetInt32(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetDouble(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetDateTime(17), dr.GetString(18), dr.GetDateTime(19)));
                }
                dr.Close();
            }
            return list;
        }

        public void Save(NegativeFileInfo TBL_MS_NEGATIVE_FILE)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                                INSERT INTO TBL_MS_NEGATIVE_FILE_TMP(
                                COD_BANK
                                ,COD_BANK_NAME
                                ,DAT_BIRTH_CUST
                                ,IDENTITY_NO
                                ,COD_CC_HOMEBRN
                                ,COD_CUST_ID
                                ,COD_DRAWEE_ACCT
                                ,NAM_CUST_FULL
                                ,REF_INSTR_NO_REJECT
                                ,AMT_TXN_LCY
                                ,COD_DHIB_STAT
                                ,DAT_NSF_REJ
                                ,COD_ORG_BRN
                                ,CUST_ADDRESS
                                ,CUST_PHONE
                                ,ENTRY_BY
                                ,ENTRY_DATE
                                ,UPDATE_BY
                                ,UPDATE_DATE
                                ) 
                                VALUES(
                                @COD_BANK
                                ,LEFT(@COD_BANK_NAME,40)
                                ,@DAT_BIRTH_CUST
                                ,LEFT(@IDENTITY_NO,30)
                                ,LEFT(@COD_CC_HOMEBRN,6)
                                ,@COD_CUST_ID
                                ,@COD_DRAWEE_ACCT
                                ,LEFT(@NAM_CUST_FULL,120)
                                ,LEFT(@REF_INSTR_NO_REJECT,36)
                                ,@AMT_TXN_LCY
                                ,LEFT(@COD_DHIB_STAT,15)
                                ,@DAT_NSF_REJ
                                ,@COD_ORG_BRN
                                ,@CUST_ADDRESS
                                ,@CUST_PHONE
                                ,@ENTRY_BY
                                ,@ENTRY_DATE
                                ,@UPDATE_BY
                                ,@UPDATE_DATE
                                );
                                ");
                    db.AddInParameter(cmd, "@COD_BANK", DbType.Int32, TBL_MS_NEGATIVE_FILE.COD_BANK);
                    db.AddInParameter(cmd, "@COD_BANK_NAME", DbType.String, TBL_MS_NEGATIVE_FILE.COD_BANK_NAME);
                    db.AddInParameter(cmd, "@DAT_BIRTH_CUST", DbType.DateTime, TBL_MS_NEGATIVE_FILE.DAT_BIRTH_CUST);
                    db.AddInParameter(cmd, "@IDENTITY_NO", DbType.String, TBL_MS_NEGATIVE_FILE.IDENTITY_NO);
                    db.AddInParameter(cmd, "@COD_CC_HOMEBRN", DbType.String, TBL_MS_NEGATIVE_FILE.COD_CC_HOMEBRN);
                    db.AddInParameter(cmd, "@COD_CUST_ID", DbType.Int32, TBL_MS_NEGATIVE_FILE.COD_CUST_ID);
                    db.AddInParameter(cmd, "@COD_DRAWEE_ACCT", DbType.String, TBL_MS_NEGATIVE_FILE.COD_DRAWEE_ACCT);
                    db.AddInParameter(cmd, "@NAM_CUST_FULL", DbType.String, TBL_MS_NEGATIVE_FILE.NAM_CUST_FULL);
                    db.AddInParameter(cmd, "@REF_INSTR_NO_REJECT", DbType.String, TBL_MS_NEGATIVE_FILE.REF_INSTR_NO_REJECT);
                    db.AddInParameter(cmd, "@AMT_TXN_LCY", DbType.Double, TBL_MS_NEGATIVE_FILE.AMT_TXN_LCY);
                    db.AddInParameter(cmd, "@COD_DHIB_STAT", DbType.String, TBL_MS_NEGATIVE_FILE.COD_DHIB_STAT);
                    db.AddInParameter(cmd, "@DAT_NSF_REJ", DbType.DateTime, TBL_MS_NEGATIVE_FILE.DAT_NSF_REJ);
                    db.AddInParameter(cmd, "@COD_ORG_BRN", DbType.String, TBL_MS_NEGATIVE_FILE.COD_ORG_BRN);
                    db.AddInParameter(cmd, "@CUST_ADDRESS", DbType.String, TBL_MS_NEGATIVE_FILE.CUST_ADDRESS);
                    db.AddInParameter(cmd, "@CUST_PHONE", DbType.String, TBL_MS_NEGATIVE_FILE.CUST_PHONE);
                    db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_MS_NEGATIVE_FILE.ENTRY_BY);
                    db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_MS_NEGATIVE_FILE.ENTRY_DATE);
                    db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_MS_NEGATIVE_FILE.UPDATE_BY);
                    db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_MS_NEGATIVE_FILE.UPDATE_DATE); 
                    db.ExecuteNonQuery(cmd);
        }

        public int Approve(string RowID)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();
                DbCommand cmd;

                string sql;
                sql = @"
                    INSERT INTO TBL_MS_NEGATIVE_FILE
                    (
	                    [COD_BANK]
	                    ,[COD_BANK_NAME]
	                    ,[DAT_BIRTH_CUST]
	                    ,[IDENTITY_NO]
	                    ,[COD_CC_HOMEBRN]
	                    ,[COD_CUST_ID]
	                    ,[COD_DRAWEE_ACCT]
	                    ,[NAM_CUST_FULL]
	                    ,[REF_INSTR_NO_REJECT]
	                    ,[AMT_TXN_LCY]
	                    ,[COD_DHIB_STAT]
	                    ,[DAT_NSF_REJ]
	                    ,[COD_ORG_BRN]
	                    ,[CUST_ADDRESS]
	                    ,[CUST_PHONE]
	                    ,[ENTRY_BY]
	                    ,[ENTRY_DATE]
	                    ,[UPDATE_BY]
	                    ,[UPDATE_DATE]
                    )
                    SELECT
	                    [COD_BANK]
	                    ,[COD_BANK_NAME]
	                    ,[DAT_BIRTH_CUST]
	                    ,[IDENTITY_NO]
	                    ,[COD_CC_HOMEBRN]
	                    ,[COD_CUST_ID]
	                    ,[COD_DRAWEE_ACCT]
	                    ,[NAM_CUST_FULL]
	                    ,[REF_INSTR_NO_REJECT]
	                    ,[AMT_TXN_LCY]
	                    ,[COD_DHIB_STAT]
	                    ,[DAT_NSF_REJ]
	                    ,[COD_ORG_BRN]
	                    ,[CUST_ADDRESS]
	                    ,[CUST_PHONE]
	                    ,[ENTRY_BY]
	                    ,[ENTRY_DATE]
	                    ,[UPDATE_BY]
	                    ,[UPDATE_DATE]
                    FROM TBL_MS_NEGATIVE_FILE_TMP
                    WHERE ROW_ID LIKE '' + @ROW_ID + '';

                    DELETE FROM TBL_MS_NEGATIVE_FILE_TMP
                    WHERE ROW_ID LIKE '' + @ROW_ID + '';
                    "
                    ;
                cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@ROW_ID", DbType.String, RowID);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }

        public int Reject(string RowID)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                string sql;
                DbCommand cmd;

                sql = @"
                DELETE FROM TBL_MS_NEGATIVE_FILE_TMP
                WHERE (ROW_ID LIKE '' + @ROW_ID + '');
                ";
                cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@ROW_ID", DbType.String, RowID);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }

    }
 }

